from typing import Union

from sqlalchemy import update, CursorResult, text, func
from sqlmodel import select, desc, col, asc
from sqlmodel.sql._expression_select_cls import SelectOfScalar, Select

from src.base.beans import PageResult
from src.config.db import mysqlClient
from src.domain.sys_dept import SysDept, SysDeptReq


def get_dept_by_id(dept_id: int) -> SysDept:
    return mysqlClient.exec(select(SysDept).where(SysDept.dept_id == dept_id).where(SysDept.del_flag == False)).first()


def where_handle(stmt: Union[Select, SelectOfScalar], entity: SysDept = None):
    stmt.where(SysDept.del_flag == False)
    if entity:
        if entity.dept_id:
            stmt = stmt.where(SysDept.dept_id == entity.dept_id)
        if entity.dept_name:
            stmt = stmt.where(SysDept.dept_name == entity.dept_name)
        if entity.status:
            stmt = stmt.where(SysDept.status == entity.status)
    return stmt


def get_dept_list_by_condition(entity: SysDept = None) -> list[SysDept]:
    stmt = select(SysDept)
    stmt = where_handle(stmt, entity)
    # order_by() 是返回一个新的语句对象，而不是原地修改
    stmt = stmt.order_by(desc(SysDept.sort))
    users = mysqlClient.exec(stmt)
    return users


def get_dept_page_by_condition(entity: SysDeptReq = None) -> PageResult:
    search = SysDept(**entity.model_dump())
    list_stmt = select(SysDept)
    list_stmt = where_handle(list_stmt, search)
    if entity.sorter:
        list_stmt = list_stmt.order_by(text(entity.sorter))
    else:
        list_stmt = list_stmt.order_by(asc(SysDept.sort))
    list_stmt = list_stmt.limit(entity.size).offset(entity.size * (entity.current - 1))
    users = mysqlClient.exec(list_stmt).all()

    total_stmt = select(func.count(SysDept.user_id))
    total_stmt = where_handle(total_stmt, search)
    total_result = mysqlClient.exec(total_stmt)
    total = total_result.one_or_none()
    return PageResult(total=total, data=users, current=entity.current, size=entity.size)


def insert_dept(entity: SysDept = None):
    mysqlClient.add(entity)


def update_dept(entity: SysDept = None) -> int:
    stmt = update(SysDept).values(entity.model_dump()).where(col(SysDept.dept_id).is_(entity.dept_id))
    result: CursorResult = mysqlClient.exec(stmt)
    mysqlClient.commit()
    return result.rowcount()


def delete_dept(dept_id: int = None) -> int:
    stmt = update(SysDept).values(SysDept(del_flag=True).model_dump()).where(col(SysDept.dept_id).is_(dept_id))
    result: CursorResult = mysqlClient.exec(stmt)
    mysqlClient.commit()
    return result.rowcount()


def delete_depts(dept_ids: list[int] = None) -> int:
    stmt = update(SysDept).values(SysDept(del_flag=True).model_dump()).where(col(SysDept.dept_id).in_(dept_ids))
    result: CursorResult = mysqlClient.exec(stmt)
    mysqlClient.commit()
    return result.rowcount()


def get_dept_and_child_list(dept_ids: list[int]) -> list[SysDept]:
    menu_sql = text(f"WITH RECURSIVE dept_tree AS ("
                    f"SELECT dept_id, parent_id, dept_name FROM sys_dept WHERE dept_id in :dept_ids "
                    f"UNION ALL "
                    f"SELECT d.dept_id, d.parent_id, d.dept_name  FROM sys_dept d INNER JOIN dept_tree t ON d.parent_id = t.dept_id)"
                    f"SELECT * FROM dept_tree")
    menu_stmt = select(SysDept).from_statement(menu_sql)
    return mysqlClient.exec(menu_stmt, params={"dept_ids": tuple(dept_ids)}).scalars().all()
